package com.sbtr.business.datainfo.service.impl;

import com.alibaba.druid.sql.SQLUtils;
import com.alibaba.druid.sql.ast.SQLStatement;
import com.alibaba.druid.util.JdbcConstants;
import com.alibaba.druid.util.JdbcUtils;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.sbtr.open.datainfo.dto.ConnectInfoDto;
import com.sbtr.open.datainfo.dto.SQLRequestDto;
import com.sbtr.business.datainfo.entity.DataConnectInfo;
import com.sbtr.business.datainfo.entity.DbConnection;
import com.sbtr.business.datainfo.mapper.ConnectInfoMapper;
import com.sbtr.business.datainfo.service.ConnectInfoService;
import com.sbtr.util.ConnectionUtils;
import com.sbtr.util.SQLReadUtils;
import com.sbtr.util.BeanCopyUtils;
import org.apache.commons.lang.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Base64;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import java.util.stream.Collectors;

/**
 * @author laimh
 * @version 创建时间：2020年5月8日 下午3:40:48
 * @Description 类描述
 */

@Transactional
@Service
public class ConnectInfoServiceImpl extends ServiceImpl<ConnectInfoMapper, DataConnectInfo>
		implements ConnectInfoService {
	private static final Logger logger = LoggerFactory.getLogger(ConnectInfoServiceImpl.class);

	private static String reg = ";";
	private static Pattern sqlPattern = Pattern.compile(reg, Pattern.LITERAL);

	@Autowired
    ConnectionUtils utils;

	@Override
	public List<ConnectInfoDto> getConnectInfoDto(String creater) {
		List<ConnectInfoDto> data = baseMapper.queryDatabaseHistory(creater);
		for (ConnectInfoDto connectInfoDto : data) {
			String password = connectInfoDto.getPassword();
			String encodedPassword = Base64.getEncoder().encodeToString(password.getBytes());
			connectInfoDto.setPassword(encodedPassword);
		}
		return data;
	}

	@Override
	public int updataDatabase(Long id, String user, String password) {
		byte[] decodedBytes = Base64.getDecoder().decode(password);
		String decodedString = new String(decodedBytes);
		DataConnectInfo dataInfo = baseMapper.selectById(id);
		dataInfo.setUser(user);
		dataInfo.setPassword(decodedString);
		int code = checkConnection(dataInfo);
		if (code == 200) {
			baseMapper.updateById(dataInfo);
		}
		return code;

	}

	@Override
	public boolean deleteDatabase(Long id) {
		DataConnectInfo dataInfo = baseMapper.selectById(id);
		dataInfo.setActivate("delete");
		baseMapper.updateById(dataInfo);
		return true;
	}

	@Override
	public int insertDatabase(ConnectInfoDto dto) {
		byte[] decodedBytes = Base64.getDecoder().decode(dto.getPassword());
		String decodedString = new String(decodedBytes);
		dto.setPassword(decodedString);
		DataConnectInfo dataInfo = BeanCopyUtils.copyProperties(dto, DataConnectInfo.class);
		QueryWrapper<DataConnectInfo> wrapper = new QueryWrapper<>();
		wrapper.in("name", dataInfo.getName());
		wrapper.in("creater", dataInfo.getCreater());
		wrapper.isNull("activate");
		DataConnectInfo selectOne = baseMapper.selectOne(wrapper);
		// 连接名已经纯在
		if (selectOne != null) {
			return 300;
		}

		int code = checkConnection(dataInfo);
		if (code == 200) {
			dataInfo.setId(System.currentTimeMillis());
			baseMapper.insert(dataInfo);
		}
		return code;
	}

	public int checkConnection(DataConnectInfo dataInfo) {
		// hive,mysql
		String url = "";
		String className = "";
		if (ConnectionUtils.MYSQL.equals(dataInfo.getPlatform().toLowerCase())) {
			StringBuilder urlBuilder = new StringBuilder();
			urlBuilder.append("jdbc:mysql://").append(dataInfo.getHost()).append(":").append(dataInfo.getPort())
					.append("/").append(dataInfo.getDb())
					.append("?useUnicode=true&characterEncoding=UTF-8&useSSL=false");
			url = urlBuilder.toString();
			className = "com.mysql.jdbc.Driver";
		} else if (ConnectionUtils.HIVE.equals(dataInfo.getPlatform().toLowerCase())) {
			StringBuilder urlBuilder = new StringBuilder();
			urlBuilder.append("jdbc:hive2://").append(dataInfo.getHost()).append(":").append(dataInfo.getPort())
					.append("/").append(dataInfo.getDb());

			url = urlBuilder.toString();
			className = "org.apache.hive.jdbc.HiveDriver";
		}
		Connection con = null;
		try {
			try {
				Class.forName(className);
			} catch (ClassNotFoundException e) {
				e.printStackTrace();
			}
			con = DriverManager.getConnection(url, dataInfo.getUser(), dataInfo.getPassword());
			return 200;
		} catch (SQLException e) {
			int errorCode = e.getErrorCode();
			String message = e.getMessage();
			if (ConnectionUtils.HIVE.equals(dataInfo.getPlatform().toLowerCase())) {
				if (message.contains("Error validating the login")) {
					return 1045;
				}
			}
			System.out.println(errorCode);
			System.out.println(message);
			return errorCode;
		} finally {
			try {
				if (con != null)
					con.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	@Override
	public List<String> getTableList(Long id) {
		// TODO Auto-generated method stub
		DataConnectInfo dataConnectInfo = this.getById(id);
		if (dataConnectInfo == null) {
			logger.error("没有找到数据库id为{}的相关信息", id);
			throw new RuntimeException("没有找到数据库id为" + id + "的相关信息");
		}

		if (ConnectionUtils.MYSQL.equals(dataConnectInfo.getPlatform().toLowerCase())) {
			DbConnection dbConnection = getMySQLDbConnection(id, dataConnectInfo);
			StringBuilder sqlBuilder = new StringBuilder();
			sqlBuilder.append("SELECT table_name FROM information_schema.tables WHERE table_schema = '")
					.append(dataConnectInfo.getDb()).append("' AND table_type = 'base table'");
			try {
				List<Map<String, Object>> tablbMapList = utils.executionSql(sqlBuilder.toString(), dbConnection);
				List<String> tableList = tablbMapList.stream().map(x -> x.get("table_name").toString())
						.collect(Collectors.toList());
				return tableList;
			} catch (Exception e) {
				e.printStackTrace();
				logger.error("数据库id为{}, 获取所有表失败", id);
				throw new RuntimeException(e.getMessage());
			}
		} else if (ConnectionUtils.HIVE.equals(dataConnectInfo.getPlatform().toLowerCase())) {
			DbConnection dbConnection = getHiveDbConnection(id, dataConnectInfo);
			StringBuilder sqlBuilder = new StringBuilder();
			sqlBuilder.append("show tables in ").append(dataConnectInfo.getDb());
			try {
				List<Map<String, Object>> tablbMapList = utils.executionSql(sqlBuilder.toString(), dbConnection);
				List<String> tableList = tablbMapList.stream().map(x -> x.get("tab_name").toString())
						.collect(Collectors.toList());
				return tableList;
			} catch (Exception e) {
				e.printStackTrace();
				logger.error("数据库id为{}, 获取所有表失败", id);
				throw new RuntimeException(e.getMessage());
			}
		} else {
			logger.error("数据库id为{} 数据库类型不支持", id);
			throw new RuntimeException("数据库id为" + id + " 数据库类型不支持");

		}
	}

	@Override
	public List<Map<String, Object>> getByTable(Long id, String tableName) {
		// TODO Auto-generated method stub
		DataConnectInfo dataConnectInfo = this.getById(id);
		if (dataConnectInfo == null) {
			logger.error("没有找到数据库id为{}的相关信息", id);
			throw new RuntimeException("没有找到数据库id为" + id + "的相关信息");
		}

		if (ConnectionUtils.MYSQL.equals(dataConnectInfo.getPlatform().toLowerCase())) {
			DbConnection dbConnection = getMySQLDbConnection(id, dataConnectInfo);
			try {
				List<Map<String, Object>> tableMapList = utils.getItemsInTableLimit(tableName, dbConnection, 10);
				return tableMapList;
			} catch (Exception e) {
				e.printStackTrace();
				logger.error("数据库id为{}, 查询数据库时失败", id);
				throw new RuntimeException(e.getMessage());
			}
		} else if (ConnectionUtils.HIVE.equals(dataConnectInfo.getPlatform().toLowerCase())) {
			DbConnection dbConnection = getHiveDbConnection(id, dataConnectInfo);
			try {
				List<Map<String, Object>> tableMapList = utils.getItemsInTableLimit(tableName, dbConnection, 10);
				return tableMapList;
			} catch (Exception e) {
				e.printStackTrace();
				logger.error("数据库id为{}, 查询数据库时失败", id);
				throw new RuntimeException(e.getMessage());
			}
		} else {
			logger.error("数据库id为{} 数据库类型不支持", id);
			throw new RuntimeException("数据库id为" + id + " 数据库类型不支持");
		}
	}

	@Override
	public List<Map<String, Object>> getBySql(SQLRequestDto requestDto) {
		// TODO Auto-generated method stub
		Long id = requestDto.getId();
		String sql = requestDto.getSql();

		if (sql == null || id == null) {
			throw new RuntimeException("id或sql不能为空");
		}

		DataConnectInfo dataConnectInfo = this.getById(id);
		if (dataConnectInfo == null) {
			logger.error("没有找到数据库id为{}的相关信息", id);
			throw new RuntimeException("没有找到数据库id为" + id + "的相关信息");
		}

		if (sql.length() < 1) {
			logger.error("查询语句不能为空");
			throw new RuntimeException("查询语句不能为空");
		}

		if (ConnectionUtils.MYSQL.equals(dataConnectInfo.getPlatform().toLowerCase())) {
			String sqlTrim = StringUtils.trim(sql);

			sqlTrim = sqlTrim.replaceAll("[;]{1,}", ";");

			Matcher match = sqlPattern.matcher(sqlTrim);
			if (match.find()) {
				if (match.start() != (sqlTrim.length() - 1)) {
					logger.error("查询语句多于一条");
					throw new RuntimeException("sql语句不能多于一条");
				}
			}

			String limitSql = sqlTrim;
			try {
				limitSql = SQLReadUtils.limit(sql, JdbcUtils.MYSQL, 10);
			} catch (IllegalArgumentException illegale) {
				logger.error(illegale.getMessage());
				throw new RuntimeException(illegale.getMessage());
			} catch (UnsupportedOperationException unsupporte) {
				logger.error("数据库id为{} 数据库类型不支持", id);
				throw new RuntimeException("数据库id为" + id + " 数据库类型不支持");
			}

			// this.checkoutIllegal(sqlTrim, ConnectionUtils.MYSQL);

			// sqlTrim = sqlTrim.replaceAll("[;]{1,}", "");
			// String[] sqlArray = sqlTrim.split("\\s+");
			// if (!ConnectionUtils.SELECT.equals(sqlArray[0].toLowerCase())) {
			// logger.error("非查询语句");
			// throw new RuntimeException("非查询语句");
			// }

			// StringBuilder limitSqlBuilder = new StringBuilder();
			// limitSqlBuilder.append("select * from
			// (").append(sqlTrim).append(") limitResult limit 10");
			// String limitSql = limitSqlBuilder.toString();
			try {
				DbConnection dbConnection = getMySQLDbConnection(id, dataConnectInfo);
				logger.info("执行的sql为{}", limitSql);
				List<Map<String, Object>> tableMapList = utils.executionSql(limitSql, dbConnection);
				return tableMapList;
			} catch (Exception e) {
				e.printStackTrace();
				logger.error("数据库id为{}, 获取所有表失败", id);
				throw new RuntimeException(e.getMessage());
			}
		} else if (ConnectionUtils.HIVE.equals(dataConnectInfo.getPlatform().toLowerCase())) {
			String sqlTrim = StringUtils.trim(sql);

			sqlTrim = sqlTrim.replaceAll("[;]{1,}", ";");

			Matcher match = sqlPattern.matcher(sqlTrim);
			if (match.find()) {
				if (match.start() != (sqlTrim.length() - 1)) {
					logger.error("查询语句多于一条");
					throw new RuntimeException("sql语句不能多于一条");
				}
			}

			String limitSql = sqlTrim;
			try {
				limitSql = SQLReadUtils.limit(sql, JdbcUtils.HIVE, 10);
			} catch (IllegalArgumentException illegale) {
				logger.error(illegale.getMessage());
				throw new RuntimeException(illegale.getMessage());
			} catch (UnsupportedOperationException unsupporte) {
				logger.error("数据库id为{} 数据库类型不支持", id);
				throw new RuntimeException("数据库id为" + id + " 数据库类型不支持");
			}

			// this.checkoutIllegal(sqlTrim, ConnectionUtils.HIVE);
			//
			// sqlTrim = sqlTrim.replaceAll("[;]{1,}", "");
			// String[] sqlArray = sqlTrim.split("\\s+");
			// if (!ConnectionUtils.SELECT.equals(sqlArray[0].toLowerCase())) {
			// logger.error("非查询语句");
			// throw new RuntimeException("非查询语句");
			// }
			//
			// StringBuilder limitSqlBuilder = new StringBuilder();
			// limitSqlBuilder.append("select * from
			// (").append(sqlTrim).append(") limitResult limit 10");
			// String limitSql = limitSqlBuilder.toString();

			try {
				DbConnection dbConnection = getHiveDbConnection(id, dataConnectInfo);
				logger.info("执行的sql为{}", limitSql);
				List<Map<String, Object>> tableMapList = utils.executionSql(limitSql, dbConnection);
				return tableMapList;
			} catch (Exception e) {
				e.printStackTrace();
				logger.error("数据库id为{}, 获取所有表失败", id);
				throw new RuntimeException(e.getMessage());
			}
		} else {
			logger.error("数据库id为{} 数据库类型不支持", id);
			throw new RuntimeException("数据库id为" + id + " 数据库类型不支持");
		}
	}

	@Override
	public List<Map<String, Object>> getAllBySql(SQLRequestDto requestDto) {
		// TODO Auto-generated method stub
		Long id = requestDto.getId();
		String sql = requestDto.getSql();

		if (sql == null || id == null) {
			throw new RuntimeException("id或sql不能为空");
		}

		DataConnectInfo dataConnectInfo = this.getById(id);
		if (dataConnectInfo == null) {
			logger.error("没有找到数据库id为{}的相关信息", id);
			throw new RuntimeException("没有找到数据库id为" + id + "的相关信息");
		}

		if (sql.length() < 1) {
			logger.error("查询语句不能为空");
			throw new RuntimeException("查询语句不能为空");
		}

		if (ConnectionUtils.MYSQL.equals(dataConnectInfo.getPlatform().toLowerCase())) {
			String sqlTrim = StringUtils.trim(sql);

			sqlTrim = sqlTrim.replaceAll("[;]{1,}", ";");

			Matcher match = sqlPattern.matcher(sqlTrim);
			if (match.find()) {
				if (match.start() != (sqlTrim.length() - 1)) {
					logger.error("查询语句多于一条");
					throw new RuntimeException("sql语句不能多于一条");
				}
			}

			String limitSql = sqlTrim;
			try {
				limitSql = SQLReadUtils.limit(sql, JdbcUtils.MYSQL, Integer.MAX_VALUE);
			} catch (IllegalArgumentException illegale) {
				logger.error(illegale.getMessage());
				throw new RuntimeException(illegale.getMessage());
			} catch (UnsupportedOperationException unsupporte) {
				logger.error("数据库id为{} 数据库类型不支持", id);
				throw new RuntimeException("数据库id为" + id + " 数据库类型不支持");
			}

			// this.checkoutIllegal(sqlTrim, ConnectionUtils.MYSQL);

			// sqlTrim = sqlTrim.replaceAll("[;]{1,}", "");
			// String[] sqlArray = sqlTrim.split("\\s+");
			// if (!ConnectionUtils.SELECT.equals(sqlArray[0].toLowerCase())) {
			// logger.error("非查询语句");
			// throw new RuntimeException("非查询语句");
			// }

			// StringBuilder limitSqlBuilder = new StringBuilder();
			// limitSqlBuilder.append("select * from
			// (").append(sqlTrim).append(") limitResult limit 10");
			// String limitSql = limitSqlBuilder.toString();
			try {
				DbConnection dbConnection = getMySQLDbConnection(id, dataConnectInfo);
				logger.info("执行的sql为{}", limitSql);
				List<Map<String, Object>> tableMapList = utils.executionSql(limitSql, dbConnection);
				return tableMapList;
			} catch (Exception e) {
				e.printStackTrace();
				logger.error("数据库id为{}, 获取所有表失败", id);
				throw new RuntimeException(e.getMessage());
			}
		} else if (ConnectionUtils.HIVE.equals(dataConnectInfo.getPlatform().toLowerCase())) {
			String sqlTrim = StringUtils.trim(sql);

			sqlTrim = sqlTrim.replaceAll("[;]{1,}", ";");

			Matcher match = sqlPattern.matcher(sqlTrim);
			if (match.find()) {
				if (match.start() != (sqlTrim.length() - 1)) {
					logger.error("查询语句多于一条");
					throw new RuntimeException("sql语句不能多于一条");
				}
			}

			String limitSql = sqlTrim;
			try {
				limitSql = SQLReadUtils.limit(sql, JdbcUtils.HIVE, Integer.MAX_VALUE);
			} catch (IllegalArgumentException illegale) {
				logger.error(illegale.getMessage());
				throw new RuntimeException(illegale.getMessage());
			} catch (UnsupportedOperationException unsupporte) {
				logger.error("数据库id为{} 数据库类型不支持", id);
				throw new RuntimeException("数据库id为" + id + " 数据库类型不支持");
			}

			// this.checkoutIllegal(sqlTrim, ConnectionUtils.HIVE);
			//
			// sqlTrim = sqlTrim.replaceAll("[;]{1,}", "");
			// String[] sqlArray = sqlTrim.split("\\s+");
			// if (!ConnectionUtils.SELECT.equals(sqlArray[0].toLowerCase())) {
			// logger.error("非查询语句");
			// throw new RuntimeException("非查询语句");
			// }
			//
			// StringBuilder limitSqlBuilder = new StringBuilder();
			// limitSqlBuilder.append("select * from
			// (").append(sqlTrim).append(") limitResult limit 10");
			// String limitSql = limitSqlBuilder.toString();

			try {
				DbConnection dbConnection = getHiveDbConnection(id, dataConnectInfo);
				logger.info("执行的sql为{}", limitSql);
				List<Map<String, Object>> tableMapList = utils.executionSql(limitSql, dbConnection);
				return tableMapList;
			} catch (Exception e) {
				e.printStackTrace();
				logger.error("数据库id为{}, 获取所有表失败", id);
				throw new RuntimeException(e.getMessage());
			}
		} else {
			logger.error("数据库id为{} 数据库类型不支持", id);
			throw new RuntimeException("数据库id为" + id + " 数据库类型不支持");
		}
	}

	private DbConnection getMySQLDbConnection(Long id, DataConnectInfo dataConnectInfo) {
		DbConnection dbConnection = new DbConnection();
		dbConnection.setDbType(ConnectionUtils.MYSQL);
		dbConnection.setDriverClassName("com.mysql.jdbc.Driver");
		dbConnection.setMaxActive(20);
		dbConnection.setMaxWait(600000);
		dbConnection.setInitialSize(2);
		dbConnection.setUsername(dataConnectInfo.getUser());
		dbConnection.setPassword(dataConnectInfo.getPassword());
		StringBuilder urlBuilder = new StringBuilder();
		urlBuilder.append("jdbc:mysql://").append(dataConnectInfo.getHost()).append(":")
				.append(dataConnectInfo.getPort()).append("/").append(dataConnectInfo.getDb())
				.append("?useUnicode=true&characterEncoding=UTF-8&useSSL=false");

		dbConnection.setUrl(urlBuilder.toString());
		return dbConnection;
	}

	private DbConnection getHiveDbConnection(Long id, DataConnectInfo dataConnectInfo) {
		DbConnection dbConnection = new DbConnection();
		dbConnection.setDbType(ConnectionUtils.HIVE);
		dbConnection.setDriverClassName("org.apache.hive.jdbc.HiveDriver");
		dbConnection.setMaxActive(20);
		dbConnection.setMaxWait(600000);
		dbConnection.setInitialSize(2);
		dbConnection.setUsername(dataConnectInfo.getUser());
		dbConnection.setPassword(dataConnectInfo.getPassword());
		StringBuilder urlBuilder = new StringBuilder();
		urlBuilder.append("jdbc:hive2://").append(dataConnectInfo.getHost()).append(":")
				.append(dataConnectInfo.getPort()).append("/").append(dataConnectInfo.getDb());

		dbConnection.setUrl(urlBuilder.toString());
		return dbConnection;
	}

	@Override
	public boolean checkoutIllegal(String sql, String type) {
		// TODO Auto-generated method stub
		try {
			String dbType = null;
			if (type.equals(ConnectionUtils.MYSQL)) {
				dbType = JdbcConstants.MYSQL;
			} else if (type.equals(ConnectionUtils.MYSQL)) {
				dbType = JdbcConstants.HIVE;
			}
			List<SQLStatement> stmtList = SQLUtils.parseStatements(sql, dbType);
			return true;
		} catch (Exception e) {
			logger.error("sql校验失败");
			e.printStackTrace();
			throw new RuntimeException(e.getMessage());

		}

	}

}
